package com.loie.datacenter.util;

import com.alibaba.fastjson.JSONObject;
import com.loie.datacenter.comm.ExcelConst ;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import sun.misc.BASE64Decoder;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
import java.util.UUID;
import org.apache.poi.POIXMLTypeLoader;

/**
 * @author zhl
 * @create 2021/10/18 18:18
 */
public class ExcelFilenameUtil {
    public static void getExcel(String filename, List<List<String>> tableList, List<JSONObject> headList,Boolean isphoto,String  sheetName) throws IOException {
//       SXSSFWorkbook wb = null;
        Workbook wb= null;
        FileInputStream fis = null;
        //ZipSecureFile.setMinInflateRatio(0.01);
        //ZipSecureFile.setMinInflateBytes(1024  * 1024); // 最小解压1MB
        //ZipSecureFile.setMinInflateRatio(0.001);
        try {
            // 调整 Zip 炸弹防护阈值
//            POIXMLTypeLoader. DEFAULT_MAX_RECORD_SIZE = 1024 * 1024 * 10; // 例如，将最大记录大小调整为 10MB
//            POIXMLTypeLoader.DEFAULT_MAX_TOTAL_SIZE = 1024 * 1024 * 100; // 例如，将总大小调整为 100MB


            File file = new File(filename);

            if (file.exists()) {
                fis = new FileInputStream(file);
                ZipSecureFile.setMinInflateRatio(-1.0d);
//                wb = new XSSFWorkbook(fis);
                        //XSSFWorkbook(fis);
//                wb = new SXSSFWorkbook();
//                fis.close();
//                wb = new XSSFWorkbook(fis);
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fis);
                wb = new SXSSFWorkbook(xssfWorkbook);




                if (wb.getSheet(sheetName)  != null) {
                    wb.removeSheetAt(wb.getSheetIndex(sheetName));
                }

            } else {
                wb = new SXSSFWorkbook();
            }

            //wb = new SXSSFWorkbook();
            if (wb.getSheet(sheetName) != null) {
                wb.removeSheetAt(wb.getSheetIndex(sheetName));
            }
            //Sheet sheet = wb.createSheet(sheetName);
            //Sheet sheet = wb.createSheet(sheetName);

//            SXSSFSheet sheet = wb.createSheet(sheetName);
            SXSSFSheet sheet = (SXSSFSheet) wb.createSheet(sheetName);
            //org.apache.poi.xssf.streaming.SXSSFSheet  sheet = (SXSSFSheet) wb.createSheet(sheetName);
            sheet.setDefaultRowHeight((short) 300);
            CellStyle headStyle = wb.createCellStyle();
            Font font = wb.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short) 12);
            headStyle.setFont(font);
            headStyle.setWrapText(true);

            SXSSFRow row = sheet.createRow(0);
            SXSSFCell cell;

            int totalColumn = 0;
            cell = row.createCell(0);
            cell.setCellValue("#");
            sheet.setColumnWidth(0, 1000);
            cell.setCellStyle(headStyle);
            Integer pos1=-1;
            Integer pos2=-1;

            for (int i = 0; i < headList.size(); i++) {
                cell = row.createCell(i + 1);
                cell.setCellValue(headList.get(i).getString(ExcelConst.TITLE));
                if (headList.get(i).getString(ExcelConst.TITLE).equals("发票号码"))
                    pos1=i;
                if (headList.get(i).getString(ExcelConst.TITLE).equals("产品编号"))
                    pos2=i;
                cell.setCellStyle(headStyle);
                totalColumn = i + 1;
                sheet.setColumnWidth(i + 1, headList.get(i).getInteger(ExcelConst.WIDTH));
            }
            CellStyle cellStyle_basic = wb.createCellStyle();
            cellStyle_basic.setWrapText(true);
            cellStyle_basic.setVerticalAlignment(VerticalAlignment.CENTER);
            for (int i = 0; i < tableList.size(); i++) {
                row = sheet.createRow(i + 1);
                if (isphoto)
                row.setHeight((short)(85*20));
                else
                    row.setHeight((short)(30*20));

                cell = row.createCell(0);
                cell.setCellValue(i + 1);
                for (int j = 0; j < totalColumn; j++) {
                    if (headList.get(j).getString(ExcelConst.TITLE).equals("图片")) {
                        if (tableList.get(i).get(j).equals("ZW1wdHk=")) {
                            String str="";
                            if (pos1!=-1)
                              str="发票号码："+tableList.get(i).get(pos1)+"/";
                            if (pos2!=-1)
                                str=str+"产品编号："+tableList.get(i).get(pos2);
                            System.out.println(String.format("第%d行/共%d行/"+str+"图片为空:",i+1,tableList.size()));
                            continue;//empty图片为空
                        }
                        BASE64Decoder base64Decoder = new BASE64Decoder();
                        byte[] imageData = base64Decoder.decodeBuffer(tableList.get(i).get(j));

                        //byte[] imageData = tableList.get(i).get(j).getBytes();
                        if (imageData != null && imageData.length > 0) {
                            int pictureIdx = wb.addPicture(imageData, Workbook.PICTURE_TYPE_JPEG);
                            CreationHelper helper = wb.getCreationHelper();
                            Drawing drawing = sheet.createDrawingPatriarch();
                            ClientAnchor anchor = helper.createClientAnchor();
                            anchor.setCol1(j+1);
                            anchor.setRow1(i + 1);
                            Picture picture = drawing.createPicture(anchor, pictureIdx);
//                    picture.resize(2, 2);
                            picture.resize(1, 1);
                        }
                    }
                    else {


                        cell = row.createCell(j + 1);
                        if ("2".equals(headList.get(j).getString(ExcelConst.TYPE))) {
                            CellStyle cellStyle = wb.createCellStyle();
                            DataFormat df = wb.createDataFormat();
                            cellStyle.setDataFormat(df.getFormat(headList.get(j).getString(ExcelConst.FORMAT)));
                            //cellStyle.setWrapText(true);
                            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                            cell.setCellStyle(cellStyle);
                            String value = tableList.get(i).get(j);
                            cell.setCellValue(Double.parseDouble(value == null ? "0" : value));
                        } else {
//                            CellStyle cellStyle = wb.createCellStyle();
//                            cellStyle.setWrapText(true);
//                            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                            cell.setCellStyle(cellStyle_basic);
                            cell.setCellValue(tableList.get(i).get(j));

                        }
                    }

                }
            }
           // String fileName = UUID.randomUUID() + ".xlsx";
            //fileName = URLEncoder.encode(fileName, "UTF-8");
            FileOutputStream fileOut = new FileOutputStream(filename);
            wb.write(fileOut);
            fileOut.close();
//            res.setCharacterEncoding("UTF-8");
//            res.setContentType("application/octet-stream");
//            res.addHeader("Content-Disposition", "attachment;filename=" + fileName);
//            wb.write(out);
        }
     catch (IOException e) {
        e.printStackTrace();
        System.err.println(" 读取文件时出现错误: " + e.getMessage());
    } finally {
        try {
            if (fis != null) {
                fis.close();
            }
            if (wb != null) {
                wb.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

//finally {
//            if (wb != null) {
//                try {
//                    //wb.dispose();
//                    wb.close();
//                } catch (Exception e) {
//                    e.printStackTrace();
//                }
//            }
//        }
    }

    public static void getExcelWithCellRange(HttpServletResponse res, List<List<String>> tableList, List<JSONObject> headList, List<List<String>> cellRangeList) throws IOException {
        SXSSFWorkbook wb = null;
        try (OutputStream out = res.getOutputStream()) {
            wb = new SXSSFWorkbook();

            SXSSFSheet sheet = wb.createSheet();
            sheet.setDefaultRowHeight((short) 300);
            CellStyle headStyle = wb.createCellStyle();
            Font font = wb.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short) 12);
            headStyle.setFont(font);

            SXSSFRow row = sheet.createRow(0);
            SXSSFCell cell;

            int totalColumn = 0;
            for (int i = 0; i < headList.size(); i++) {
                cell = row.createCell(i);
                cell.setCellValue(headList.get(i).getString(ExcelConst.TITLE));
                cell.setCellStyle(headStyle);
                totalColumn = i + 1;
                sheet.setColumnWidth(i, headList.get(i).getInteger(ExcelConst.WIDTH));
            }
            for (int i = 0; i < tableList.size(); i++) {
                row = sheet.createRow(i + 1);
                for (int j = 0; j < totalColumn; j++) {
                    cell = row.createCell(j);
                    if ("2".equals(headList.get(j).getString(ExcelConst.TYPE))) {
                        CellStyle cellStyle = wb.createCellStyle();
                        DataFormat df = wb.createDataFormat();
                        cellStyle.setDataFormat(df.getFormat(headList.get(j).getString(ExcelConst.FORMAT)));
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                        cell.setCellStyle(cellStyle);
                        String value = tableList.get(i).get(j);
                        cell.setCellValue(Double.parseDouble(value == null ? "0" : value));
                    } else {
                        cell.setCellValue(tableList.get(i).get(j));
                    }
                }
            }
            int startPos = 1;
            int len = 0;
            String mainDataPre = cellRangeList.get(0).get(0);
            for (int i = 1; i < cellRangeList.size(); i++) {
                String mainData = cellRangeList.get(i).get(0);
                if (mainDataPre.equals(mainData)) {
                    len++;
                } else {
                    if (len > 0) {
                        for (int j = 0; j < totalColumn; j++) {
                            if ("1".equals(headList.get(j).getString(ExcelConst.MERGE))) {
                                sheet.addMergedRegion(new CellRangeAddress(startPos, startPos + len, j, j));
//                                CellStyle cellStyle = wb.createCellStyle();
//                                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//                                sheet.getRow(startPos).getCell(j).setCellStyle(cellStyle);
                            }
                        }
                        len = 0;
                    }
                    startPos = i + 1;
                }
                mainDataPre = mainData;
            }
            if (len > 0) {
                for (int j = 0; j < totalColumn; j++) {
                    if ("1".equals(headList.get(j).getString(ExcelConst.MERGE))) {
                        sheet.addMergedRegion(new CellRangeAddress(startPos, startPos + len, j, j));
                    }
                }
            }

            String fileName = UUID.randomUUID() + ".xlsx";
            fileName = URLEncoder.encode(fileName, "UTF-8");
            res.setCharacterEncoding("UTF-8");
            res.setContentType("application/octet-stream");
            res.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            wb.write(out);
        } finally {
            if (wb != null) {
                try {
                    wb.dispose();
                    wb.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
